package com.sduept.nwld.kpi.manager;

import com.sduept.core.dao.SuperDAO;
import com.sduept.nwld.dataserver.enums.FieldType;
import com.sduept.nwld.kpi.datasource.KpiDataElement;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Comparator;
import java.util.List;
import java.util.TreeSet;

@Service
public class KpiDataDbManager {
	public static final String HISTORY_TABLE_SUFFIX = "_history";
	public static final String HISTORY_TABLE_UPDATE_COLUMN = "auto_update_time";
	@Resource
	private DataSource server_datasource;
	
	@Autowired
	private SuperDAO dao;
	
	
	
	public void createTable(String tableName,
			List<KpiDataElement> fields) throws SQLException {
		Connection conn = null;
		Statement st = null;
		try {
			conn = server_datasource.getConnection();
			String sql = genPreparedStatementStr(tableName, fields);
			st = conn.prepareStatement(sql);
			st.executeUpdate(sql);
		} finally{
				st.close();
				conn.close();
		}
	}
	public void createHistoryTable(String tableName,
			List<KpiDataElement> fields) throws SQLException {
		Connection conn = null;
		Statement st = null;
		try {
			conn = server_datasource.getConnection();
			String sql = genHistoryPreparedStatementStr(tableName+HISTORY_TABLE_SUFFIX, fields);
			st = conn.prepareStatement(sql);
			st.executeUpdate(sql);
		} finally{
			st.close();
			conn.close();
		}
	}
	private String genPreparedStatementStr(String tableName,List<KpiDataElement> datas){
		//String sql ="CREATE TABLE `t_t2` (`id` INT NULL,`name` INT NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB"
		int len = datas.size();
		
		TreeSet<KpiDataElement> sortSet = new TreeSet<>(new Comparator<KpiDataElement>() {
			@Override
			public int compare(KpiDataElement o1, KpiDataElement o2) {
				return o1.getFieldOrder() - o2.getFieldOrder();
			}
		});
		
		sortSet.addAll(datas);
		
		StringBuilder sb = new StringBuilder("CREATE TABLE `"+tableName+"` (");
		int i = 0; 
		for(KpiDataElement d : sortSet){
			sb.append("`"+d.getField()+"` ");
			String nstr = d.isNotNull() ? " NOT NULL" : " NULL";
			putFileType(sb,d,nstr);
			if(i != len - 1){
				sb.append(",");
			}
			i++;
		}
		sb.append(")  COLLATE='utf8_general_ci' ENGINE=InnoDB");
		return sb.toString();
	}
	
	private void putFileType(StringBuilder sb,KpiDataElement d,String nstr){
		if(FieldType.String.name().equals(d.getType())){
			sb.append(FieldType.String.getDBType()+nstr);
		} else if(FieldType.Integer.name().equals(d.getType())){
			sb.append(FieldType.Integer.getDBType()+nstr);
		} else if(FieldType.Float.name().equals(d.getType())){
			sb.append(FieldType.Float.getDBType()+nstr);
		} else if(FieldType.Double.name().equals(d.getType())){
			sb.append(FieldType.Double.getDBType()+nstr);
		} else if(FieldType.Long.name().equals(d.getType())){
			sb.append(FieldType.Long.getDBType()+nstr);
		} else if(FieldType.DateTime.name().equals(d.getType())){
			sb.append(FieldType.DateTime.getDBType()+nstr);
		} else if(FieldType.Date.name().equals(d.getType())){
			sb.append(FieldType.Date.getDBType()+nstr);
		} else if(FieldType.File.name().equals(d.getType())){
			sb.append(FieldType.File.getDBType()+nstr);
		}else if(FieldType.Blob.name().equals(d.getType())){
			sb.append(FieldType.Blob.getDBType()+nstr);
		}
	}
	
	private String genHistoryPreparedStatementStr(String tableName,List<KpiDataElement> datas){
		//String sql ="CREATE TABLE `t_t2` (`id` INT NULL,`name` INT NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB"
		
		TreeSet<KpiDataElement> sortSet = new TreeSet<>(new Comparator<KpiDataElement>() {
			@Override
			public int compare(KpiDataElement o1, KpiDataElement o2) {
				return o1.getFieldOrder() - o2.getFieldOrder();
			}
		});
		
		sortSet.addAll(datas);
		
		StringBuilder sb = new StringBuilder("CREATE TABLE `"+tableName+"` (");
		for(KpiDataElement d : sortSet){
			sb.append("`"+d.getField()+"` ");
			String nstr = d.isNotNull() ? " NOT NULL" : " NULL";
			putFileType(sb,d,nstr);
			sb.append(",");
		}
		sb.append("`"+HISTORY_TABLE_UPDATE_COLUMN+"` TIMESTAMP NULL");
		sb.append(")  COLLATE='utf8_general_ci' ENGINE=InnoDB");
		return sb.toString();
	}
}
